MySQL LOAD DATA INFILE

Probleem

Het komt heel veel voor dat je de gegevens in een tekstbestand binnenkrijgt en dat je die dan in de tabel moet inlezen. Als voorbeeld gaan we alle boeken uit de handleiding inserten in de tabel Boeken.

Oplossing

1. kopieer de tabel in een Excel sheet;
2. verwijder de titelrij;
3. verwijder de lege rijen (ctrl klik);
4. Opslaan als:
   1. ga naar Z schijf;
   2. CSV (gescheiden door lijstscheidingsteken);
   3. geef aan het bestand de naam Boeken in de map App_Data;
5. Open het Boeken.csv bestand in KladBlok:
   1. let erop dat er geen laaste lege regel in het bestand zit;

Alles werkt prima, behalve het feit dat het scheidingsteken een puntkomma is en geen | (pipe) teken.

1. Configuratiescherm;
2. Land en taal;
3. toetsenbord of andere invoermethoden wijzigen;
4. notaties;
5. meer instellingen;
6. lijstscheidingsteken;

Vergeet niet Excel opnieuw op te starten.

Gelukkig is er het LOAD DATA INFILE statement waarmee je in Microsoft SQL Server meerdere rijen in één keer kan inserten.

Maak eerst een tekstbestand. De waarden die je in de kolommen wil plaatsen typ je op één lijn en worden gescheiden door een scheidingsteken dat je zelf kan kiezen. Gebruik liefts een teken dat zeer zelden wordt gebruikt om geen fouten te maken. Ik gebruik meestal het rechtopstaand streepje | (alt gr |). De volgorde van de eigenschappen moet overeenstemmen met de volgorde van de kolommen in de tabel:

Aurelius|Augustinus|De stad van God|Baarn|Uitgeverij Baarn|1983|1992|Nog te lezen|Theologie|JI
Diderik|Batens|Logicaboek||Garant|1999||Goed boek|Wiskunde|JI
Emile|Benveniste|Le vocabulaire des institutions Indo-Européennes. 2. Pouvoir droit religion|Paris?|Les ditions de minuit|1969||Een goed geschiedenis boek|Linguistiek|JI
Evert W.|Beth|De Wijsbegeerte der Wiskunde. Van Parmenides tot Bolzano|Antwerpen|Philosophische Biliotheek Uitgeversmij. N.V. Standaard-Boekhandel|1944|?|Een goed boek|Filosofie|JI
Evert W.|Beth|Wijsbegeerte der Wiskunde|Antwerpen|Philosophische Biliotheek Uitgeversmij. N.V. Standaard-Boekhandel|1948|?|Een goed boek|Wiskunde|JI
Rémy|Bernard|Antonin le Pieux. Le siècle d''or de Rome 138-161|?|Librairie Arthme Fayard|2005|?|Een goed boek|Geschiedenis|JI
Marc|Bloch|Rois et serfs et autres écrits sur le servage|Paris|La boutique de l''histoire|1996|?|Een goed boek|Geschiedenis|JI
Pierre|Bonte en Michel Izard|Dictionnaire de l''etnologie et de l''anthropologie|?|PUF|1991|?|Een goed boek|Anthropologie|JI
Robert|Bly|The sibling society|Londen|Persus|1996|?|Een interessant boek|Antropologie|JI
Fernand|Braudel|De middellandse zee. Het landschap en de mens|Amsterdam/Antwerpen|Uitgeverij Contanct|1992|?|Uit het Frans vertaald: La méditerranée. La part du milieu. Parijs: Librairie Armand Colin, 1966|Geschiedenis|JI
Timothy|Gowers|Wiskunde, de kortste introductie|Utrecht|Uitgeverij Het Spectrum B.V.|2003||Oorpronkelijke titel: Mathematics a very schort introduction. Oxford University Press, 2002|Wiskunde|JI

Je zal merken dat de kolom Uitgeverij niet lang genoeg is. Maak een script met de naam BoekenAlterUitgeverijAlterLength.sql om de lengte van de Uitgeverij kolom te vergroten.

-- JI
-- 20 november 2012
-- vergroot de kolom uitgeverij
-- Bestandsnaam: BoekenAlterUitgeverijAlterLength.sql
use A88586JefInghelbrecht;
alter table Boeken alter column Uitgeverij nvarchar (255);

In het Frans worden heel veel 'apostrophes' gebruikt. In SQL wordt dit teken gebruikt om een string aan te geven. Als een apostrophe of een enkelvoudige quote in de tekst staat, moet je die ontdubbelen:

Dictionnaire de l''etnologie et de l''anthropologie

Het CSV bestand lees je in met het volgende statement:

LOAD DATA INFILE 'C:\Users\YCM LLC\Desktop\HFT\BoekenJI.csv'
    INTO TABLE Boeken
    FIELDS TERMINATED BY '|'
    ENCLOSED BY ''
    LINES TERMINATED BY '\n';

Syntaxis

LOAD DATA LOCAL INFILE 'c:/temp/some-file.csv'
   INTO TABLE sample_table
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   (field_1,field_2 , field_3);

Als je meer dan één kolom hebt is het belangrijk de laatste lijn toe te voegen want anders wordt de laatste kolom overgeslagen.

LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

Als het csv bestand een rij met titels heeft kan je de volgende clausule aan de query toevoegen:

IGNORE 1 ROWS

Voer de volgende query uit wanneer het csv bestand een hoofdingrij heeft:

LOAD DATA LOCAL INFILE  'c:/temp/some-file.csv'
   INTO TABLE sample_table
      FIELDS TERMINATED BY ','
      ENCLOSED BY '"'
      LINES TERMINATED BY '\n'
      IGNORE 1 ROWS
      (field_1,field_2 , field_3);